package com.woniuxy.classteacher.uitility;

import com.woniuxy.classteacher.mapper.KnowledgeDotMapper;
import com.woniuxy.commons.entity.*;
import com.woniuxy.commons.enums.TestBankEnum;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Auther: 牟强
 * @Date: 2020/7/17/017 10:41
 * @Description: 语文上传Excel模版
 */
public class ChineseExcel {
    private KnowledgeDotMapper knowledgeDotMapper;
    // 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;


    //必须传入一个mapper查询知识点数据
    public ChineseExcel(KnowledgeDotMapper knowledgeDotMapper) {
        this.knowledgeDotMapper = knowledgeDotMapper;
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @param : fielName
     * @return
     */
    public TestPaper getExcelInfo(MultipartFile mFile, String founder, Long grade) {
        String fileName = mFile.getOriginalFilename();// 获取文件名
        System.out.println("文件名：" + fileName);
        TestPaper testPaper = null;
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            testPaper = createExcel(mFile.getInputStream(), isExcel2003, founder, grade);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return testPaper;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param :           is输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public TestPaper createExcel(InputStream is, boolean isExcel2003, String founder, Long grade) {
        TestPaper testPaper = null;
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            testPaper = readStudentExcelValue(wb, founder, grade);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return testPaper;
    }

    /**
     * 读取Excel里面学生的信息
     *
     * @param wb
     * @return
     */
    private TestPaper readStudentExcelValue(Workbook wb, String founder, Long grade) {
        //设置出题时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = sdf.format(new Date());

        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        System.out.println("行数" + this.totalRows);
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        System.out.println("列数：" + this.totalCells);
        //准备实体类
        TestPaper testPaper = new TestPaper();
        //设置出题人
        testPaper.setPaperFounder(founder);
        //设置学期
        testPaper.setPaperTerm(grade);
        //准备题型lIst
        List<TestQuestion> testQuestions = new ArrayList<>();
        int selectNext = 6;//初始进入选择题的行数
        int vacancyNext = 67;//初始进入填空题的行数
        int materialNext = 73;//初始进入材料题的行数
        int writerNext = 82;//初始进入作文的行数
        int selectBegin = 1;//初始化进入选择题次数
        int vacancyBegin = 1;//初始化进入填空题次数
        int materialBegin = 1;//初始进入材料题的次数
        int writerBegin = 1;//初始化进入作文的次数
        // 循环Excel行数
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            //科目
            String subject = null;
            // 循环Excel的列
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {

                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    if (r == 0 && c == 1) {
                        //科目名称
                        subject = cell.getStringCellValue();
                        System.out.println("科目名称：" + subject);
                        testPaper.setPaperSubject(subject);
                    } else if (r == 1 && c == 1) {
                        String paperName = cell.getStringCellValue();
                        System.out.println("试卷名称" + paperName);
                        testPaper.setPaperName(paperName);
                    } else if (r == 2 && c == 1) {
                        double score = Double.parseDouble(cell.getStringCellValue());
                        System.out.println("分数" + score);
                        testPaper.setPaperTotal(score);
                    } else if (r == 3 && c == 1) {
                        String time = cell.getStringCellValue();
                        if (time.charAt(2) == '分') {
                            time = time.substring(0, 2);
                        } else {
                            time = time.substring(0, 3);
                        }
                        System.out.println("考试时间：" + time);
                        testPaper.setPaperDuration(time);
                    } else if (r == 4 && c == 1) {
                        //设置试卷描述
                        String description = cell.getStringCellValue();
                        System.out.println("描述为：" + description);
                        testPaper.setPaperDescription(description);
                    } else if (r == selectNext) {//选择题
                        System.out.println("进入选择题第：" + selectBegin + "次");
                        selectBegin++;
                        //题型类
                        TestQuestion testQuestion = new TestQuestion();
                        //设置为选择题题库
                        testQuestion.setPaperQuestionBank(TestBankEnum.SELECT_BANK.getIndex());
                        //选择题实体类
                        SelectBank selectBank = new SelectBank();
                        //设置出题时间
                        selectBank.setSelectBirth(date);
                        //设置科目
                        selectBank.setSelectSubject(subject);
                        //设置出题人
                        selectBank.setSelectFounder(founder);
                        //设置学期
                        selectBank.setSelectTerm(grade);
                        for (int i = 0; i < 7; i++) {
                            if (i <= 6 && i != 4) {
                                //选择r行的列
                                Cell ce = row.getCell(i);
                                //都设置为String类型
                                ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //获取这个单元格的值
                                String data = ce.getStringCellValue();
                                System.out.println(data);
                                if (i == 0) {
                                    //试卷题号
                                    testQuestion.setPaperQuestionNumber(Long.valueOf(data));
                                    System.out.println("题号是：" + data);
                                } else if (i == 1) {
                                    //题目
                                    selectBank.setSelectTopic(data);
                                } else if (i == 2) {
                                    //答案
                                    selectBank.setSelectAnswer(data);
                                } else if (i == 3) {
                                    //分值
                                    selectBank.setSelectPoint(Double.parseDouble(data));
                                } else if (i == 5) {
                                    System.out.println("知识点为：" + data);
                                    //知识点，从数据库中查询
                                    Integer kpId = knowledgeDotMapper.findKpIdByKdname(data);
                                    selectBank.setSelectKnowledge(kpId);
                                } else if (i == 6) {
                                    //解析
                                    selectBank.setSelectAnalyze(data);
                                }
                            }
                        }
                        int i = r + 4;//选择的题项
                        for (int j = r + 1; j <= i; j++) {
                            //改变行数选择答案的行数
                            row = sheet.getRow(j);
                            Cell ce = row.getCell(1);
                            ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                            String itme = ce.getStringCellValue();
                            System.out.println(itme);
                            if (j == r + 1) {
                                //选择题a
                                selectBank.setSelectOptionsa(itme);
                            } else if (j == r + 2) {
                                //选择题b
                                selectBank.setSelectOptionsb(itme);
                            } else if (j == r + 3) {
                                //选择题c
                                selectBank.setSelectOptionsc(itme);
                            } else if (j == r + 4) {
                                //选择题d
                                selectBank.setSelectOptionsd(itme);
                            }
                        }
                        //设置下一个选择题的行数,此处不+1，for循环会自动+1
                        r = i;
                        //设置下一个选择题的进入行数，下次还在选择题范围内才继续更新进入条件
                        if (selectBegin < 13) {
                            selectNext = r + 1;
                        }
                        System.out.println("行数是" + r);
                        System.out.println(selectBank);
                        //将选择题放入类型题
                        testQuestion.setPaperDetailQuestion(selectBank);
                        //类型题放入List
                        testQuestions.add(testQuestion);
                    } else if (r == vacancyNext && c == 0) {//进入填空题
                        System.out.println("进入填空题第：" + vacancyBegin + "次");
                        vacancyBegin++;
                        //题型类
                        TestQuestion testQuestion = new TestQuestion();
                        //设置为填空题题库
                        testQuestion.setPaperQuestionBank(TestBankEnum.VACANCY_BANK.getIndex());
                        //填空题实体类
                        VacancyBank vacancyBank = new VacancyBank();
                        //设置出题时间
                        vacancyBank.setVacancyBirth(date);
                        //设置科目
                        vacancyBank.setVacancySubject(subject);
                        //设置出题人
                        vacancyBank.setVacancyFounder(founder);
                        //设置学期
                        vacancyBank.setVacancyTerm(grade);
                        for (int i = 0; i < 7; i++) {
                            if (i <= 6 && i != 4) {
                                //选择r行的列
                                Cell ce = row.getCell(i);
                                //都设置为String类型
                                ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //获取这个单元格的值
                                String data = ce.getStringCellValue();
                                System.out.println(data);
                                if (i == 0) {
                                    //试卷题号
                                    testQuestion.setPaperQuestionNumber(Long.valueOf(data));
                                    System.out.println("题号是：" + data);
                                } else if (i == 1) {
                                    //题目
                                    vacancyBank.setVacancyTopic(data);
                                } else if (i == 2) {
                                    //答案
                                    vacancyBank.setVacancyAnswer(data);
                                } else if (i == 3) {
                                    vacancyBank.setVacancyPoint(Double.parseDouble(data));
                                } else if (i == 5) {
                                    System.out.println("知识点为：" + data);
                                    //知识点，从数据库中查询
                                    Integer kpId = knowledgeDotMapper.findKpIdByKdname(data);
                                    vacancyBank.setVacancyKnowledge(kpId);
                                }
                            }
                        }
                        //下一次进入填空题的条件跟随行数+1，下次还在填空题范围内才继续更新进入条件
                        if (vacancyBegin < 6) {
                            vacancyNext++;
                        }
                        System.out.println(vacancyBank);
                        //将选择题放入类型题
                        testQuestion.setPaperDetailQuestion(vacancyBank);
                        //类型题放入List
                        testQuestions.add(testQuestion);
                    } else if (r == materialNext && c == 0) {//进入材料题
                        System.out.println("进入材料题第：" + materialBegin + "次");
                        materialBegin++;
                        //题型类
                        TestQuestion testQuestion = new TestQuestion();
                        //设置为材料题题库
                        testQuestion.setPaperQuestionBank(TestBankEnum.MATERIAL_BANK.getIndex());
                        //材料题实体类
                        MaterialBank materialBank = new MaterialBank();

                        materialBank.setJudgeBanks(null);
                        //设置出题时间
                        materialBank.setMaterialBirth(date);
                        //设置科目
                        materialBank.setMaterialSubject(subject);
                        //设置材料题的选择题list
                        List<SelectBank> selectBanks = new ArrayList<>();
                        //设置材料题的填空题list
                        List<VacancyBank> vacancyBanks = new ArrayList<>();
                        //设置出题人
                        materialBank.setMaterialFounder(founder);
                        //设置学期
                        materialBank.setMaterialTerm(grade);
                        //现在设置材料题号与内容
                        if (r == 73) {
                            for (int i = 0; i < 2; i++) {
                                //选择r行的列
                                Cell ce = row.getCell(i);
                                //都设置为String类型
                                ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //获取这个单元格的值
                                String data = ce.getStringCellValue();
                                System.out.println(data);
                                if (i == 0) {
                                    //试卷题号
                                    testQuestion.setPaperQuestionNumber(Long.valueOf(data));
                                } else if (i == 1) {
                                    //阅读材料
                                    materialBank.setMaterialReading(data);
                                }
                            }
                            //进入条件行数+1
                            materialNext++;
                        } else if (r == 74) {
                            SelectBank selectBank = new SelectBank();
                            //设置出题人
                            selectBank.setSelectFounder(founder);
                            //设置学期
                            selectBank.setSelectTerm(grade);
                            for (int i = 1; i < 7; i++) {
                                if (i <= 6 && i != 4) {
                                    //选择r行的列
                                    Cell ce = row.getCell(i);
                                    //都设置为String类型
                                    ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    //获取这个单元格的值
                                    String data = ce.getStringCellValue();
                                    System.out.println(data);
                                    if (i == 1) {
                                        //题目
                                        selectBank.setSelectTopic(data);
                                    } else if (i == 2) {
                                        //答案
                                        selectBank.setSelectAnswer(data);
                                    } else if (i == 3) {
                                        //分值
                                        selectBank.setSelectPoint(Double.parseDouble(data));
                                    } else if (i == 5) {
                                        System.out.println("知识点为：" + data);
                                        //知识点，从数据库中查询
                                        Integer kpId = knowledgeDotMapper.findKpIdByKdname(data);
                                        selectBank.setSelectKnowledge(kpId);
                                    } else if (i == 6) {
                                        //解析
                                        selectBank.setSelectAnalyze(data);
                                    }
                                }
                            }
                            int i = r + 4;//选择的题项
                            for (int j = r + 1; j <= i; j++) {
                                //改变行数选择答案的行数
                                row = sheet.getRow(j);
                                Cell ce = row.getCell(1);
                                ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                String itme = ce.getStringCellValue();
                                System.out.println(itme);
                                if (j == r + 1) {
                                    //选择题a
                                    selectBank.setSelectOptionsa(itme);
                                } else if (j == r + 2) {
                                    //选择题b
                                    selectBank.setSelectOptionsb(itme);
                                } else if (j == r + 3) {
                                    //选择题c
                                    selectBank.setSelectOptionsc(itme);
                                } else if (j == r + 4) {
                                    //选择题d
                                    selectBank.setSelectOptionsd(itme);
                                }
                            }
                            //设置下一个选择题的行数,此处不+1，for循环会自动+1
                            r = i;
                            System.out.println("材料选择题选择题完后下次进入的行数" + r);
                            //设置进入该条件的行数
                            materialNext = r + 1;
                            selectBanks.add(selectBank);
                        } else if (r == 79 || r == 80) {
                            VacancyBank vacancyBank = new VacancyBank();
                            //设置出题人
                            vacancyBank.setVacancyFounder(founder);
                            //设置学期
                            vacancyBank.setVacancyTerm(grade);
                            for (int i = 1; i < 7; i++) {
                                if (i <= 6 && i != 4) {
                                    //选择r行的列
                                    Cell ce = row.getCell(i);
                                    //都设置为String类型
                                    ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    //获取这个单元格的值
                                    String data = ce.getStringCellValue();
                                    System.out.println(data);
                                    if (i == 1) {
                                        //题目
                                        vacancyBank.setVacancyTopic(data);
                                    } else if (i == 2) {
                                        //答案
                                        vacancyBank.setVacancyAnswer(data);
                                    } else if (i == 3) {
                                        //分值
                                        vacancyBank.setVacancyPoint(Double.parseDouble(data));
                                    } else if (i == 5) {
                                        System.out.println("知识点为：" + data);
                                        //知识点，从数据库中查询
                                        Integer kpId = knowledgeDotMapper.findKpIdByKdname(data);
                                        vacancyBank.setVacancyKnowledge(kpId);
                                    }
                                }
                            }
                            vacancyBanks.add(vacancyBank);
                            materialNext++;//为83行再进入作准备
                            System.out.println("材料阅读题下次进入的行号" + materialNext);
                            System.out.println("材料阅读题下次进入的条件行号" + r);
                        }
                        //将选择题list放入材料中
                        materialBank.setSelectBanks(selectBanks);
                        //将填空题list放入材料中
                        materialBank.setVacancyBanks(vacancyBanks);
                        //将材料题放入类型题中
                        testQuestion.setPaperDetailQuestion(materialBank);
                        testQuestions.add(testQuestion);
                    } else if (r == writerNext && c == 0) {
                        System.out.println("进入作文题：" + writerBegin);
                        writerBegin++;
                        //题型类
                        TestQuestion testQuestion = new TestQuestion();
                        //设置为材料题题库
                        testQuestion.setPaperQuestionBank(TestBankEnum.WRITER_BANK.getIndex());
                        //填空题实体类
                        WriterBank writerBank = new WriterBank();
                        //设置出题时间
                        writerBank.setWriterBirth(date);
                        //设置科目
                        writerBank.setWriterSubject(subject);
                        //设置出题人
                        writerBank.setWriterFounder(founder);
                        //设置学期
                        writerBank.setWriterTerm(grade);
                        for (int i = 1; i < 7; i++) {
                            if (i <= 6 && i != 4) {
                                //选择r行的列
                                Cell ce = row.getCell(i);
                                //都设置为String类型
                                ce.setCellType(HSSFCell.CELL_TYPE_STRING);
                                //获取这个单元格的值
                                String data = ce.getStringCellValue();
                                System.out.println(data);
                                if (i == 0) {
                                    //试卷题号
                                    testQuestion.setPaperQuestionNumber(Long.valueOf(data));
                                    System.out.println("题号是：" + data);
                                } else if (i == 1) {
                                    //题目
                                    writerBank.setWriterTopic(data);
                                } else if (i == 2) {
                                    //作文答案
                                    writerBank.setWriterAnswer(data);
                                } else if (i == 3) {
                                    //分值
                                    writerBank.setWriterPoint(Double.parseDouble(data));
                                } else if (i == 5) {
                                    System.out.println("知识点为：" + data);
                                    //知识点，从数据库中查询
                                    Integer kpId = knowledgeDotMapper.findKpIdByKdname(data);
                                    writerBank.setWriterKnowledge(kpId);
                                }
                            }
                        }
                    }
                }
            }
        }
        //将类型题list放入试卷
        testPaper.setPaperQuestions(testQuestions);
        return testPaper;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}
